package org.zjx.swing.db;

import org.zjx.swing.bean.FontFilePath;

import java.util.List;

public class FontFilePathDB {
    private static boolean isTableCreated = false;
    private final SQLiteHelper dbHelper;

    public FontFilePathDB() {
        dbHelper = SQLiteHelper.getSingleton();
        if (!isTableCreated) {
            createTable();
            isTableCreated = true;
        }
    }

    // 创建表（包含唯一性约束）
    public void createTable() {
        String sql =
                "CREATE TABLE IF NOT EXISTS font_file_path (\n" +
                        "  id          INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
                        "  font_path   TEXT    NOT NULL UNIQUE,\n" +  // 唯一约束
                        "  remark      TEXT    NOT NULL,\n" +
                        "  checked     INTEGER NOT NULL DEFAULT 0\n" +
                        ");";
        dbHelper.createTable(sql);
    }

    // 插入或更新记录（根据 font_path 唯一性）
    public void insertOrUpdate(FontFilePath fontFile) {
        String sql =
                "INSERT INTO font_file_path (font_path, remark, checked)\n" +
                        "VALUES (?, ?, ?)\n" +
                        "ON CONFLICT(font_path)\n" +        // 冲突时（font_path 已存在）
                        "DO UPDATE SET\n" +
                        "  remark = excluded.remark,\n" +   // 更新 remark
                        "  checked = excluded.checked";     // 更新 checked
        Object[] params = {
                fontFile.getFontPath(),
                fontFile.getRemark(),
                fontFile.isChecked() ? 1 : 0        // boolean 转 INTEGER
        };
        dbHelper.executeUpdate(sql, params);
    }

    // 根据 id 删除
    public void deleteById(int id) {
        String sql = "DELETE FROM font_file_path WHERE id = ?";
        dbHelper.executeUpdate(sql, new Object[]{id});
    }

    // 根据 font_path 删除
    public void deleteByFontPath(String fontPath) {
        String sql = "DELETE FROM font_file_path WHERE font_path = ?";
        dbHelper.executeUpdate(sql, new Object[]{fontPath});
    }

    // 根据 id 查询
    public FontFilePath findById(int id) {
        String sql = "SELECT * FROM font_file_path WHERE id = ?";
        List<FontFilePath> result = dbHelper.executeQuery(sql, new Object[]{id}, FontFilePath.class);
        return result.isEmpty() ? null : result.get(0);
    }

    // 根据 font_path 查询
    public FontFilePath findByFontPath(String fontPath) {
        String sql = "SELECT * FROM font_file_path WHERE font_path = ?";
        List<FontFilePath> result = dbHelper.executeQuery(sql, new Object[]{fontPath}, FontFilePath.class);
        return result.isEmpty() ? null : result.get(0);
    }

    // 查询所有记录
    public List<FontFilePath> findAll() {
        String sql = "SELECT * FROM font_file_path";
        return dbHelper.executeQuery(sql, new Object[]{}, FontFilePath.class);
    }

    // 清空表
    public void clear() {
        dbHelper.clearData("font_file_path");
    }

    // 删除表
    public void dropTable() {
        dbHelper.dropTable("font_file_path");
    }
}
